﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;
using JinianNet.JNCms.Data;

namespace JinianNet.JNCms.SQLiteDAL
{
    public class Category :BaseDAL,ICategory
    {
        public int Insert(Models.Category model)
        {
            if (model.ID <= 0)
                model.ID = Db.CreateKey("Category");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@Title", DbType.String,200),
					new SQLiteParameter("@KeyWords", DbType.String,300),
					new SQLiteParameter("@Description", DbType.String,600),
					new SQLiteParameter("@KeyName", DbType.String,255),
					new SQLiteParameter("@TempLate", DbType.String,255),
					new SQLiteParameter("@ChildTempLate", DbType.String,255),
					new SQLiteParameter("@IsVerify", DbType.Boolean,1),
					new SQLiteParameter("@Rank", DbType.Int32,4),
					new SQLiteParameter("@ParentID", DbType.Int32,4),
					new SQLiteParameter("@TypeID", DbType.Int32,4),
					new SQLiteParameter("@Count", DbType.Int32,4),
					new SQLiteParameter("@CreateTime", DbType.DateTime),
					new SQLiteParameter("@IsUrl", DbType.Boolean,1),
					new SQLiteParameter("@Url", DbType.String,255)};
            parameters[0].Value = model.Title;
            parameters[1].Value = model.KeyWords;
            parameters[2].Value = model.Description;
            parameters[3].Value = model.KeyName;
            parameters[4].Value = model.TempLate;
            parameters[5].Value = model.ChildTempLate;
            parameters[6].Value = model.IsVerify;
            parameters[7].Value = model.Rank;
            parameters[8].Value = model.ParentID;
            parameters[9].Value = model.TypeID;
            parameters[10].Value = model.Count;
            parameters[11].Value = model.CreateTime;
            parameters[12].Value = model.IsUrl;
            parameters[13].Value = model.Url;
            return Db.Helper.ExecuteNonQuery(CommandType.Text, "insert into JNCms_Category([Title],[KeyWords],[Description],[KeyName],[TempLate],[ChildTempLate],[IsVerify],[Rank],[ParentID],[TypeID],[Count],[CreateTime],[IsUrl],[Url]) values (@Title,@KeyWords,@Description,@KeyName,@TempLate,@ChildTempLate,@IsVerify,@Rank,@ParentID,@TypeID,@Count,@CreateTime,@IsUrl,@Url)", parameters);
        }

        public int Update(Models.Category model)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@Title", DbType.String,200),
					new SQLiteParameter("@KeyWords", DbType.String,300),
					new SQLiteParameter("@Description", DbType.String,600),
					new SQLiteParameter("@KeyName", DbType.String,255),
					new SQLiteParameter("@TempLate", DbType.String,255),
					new SQLiteParameter("@ChildTempLate", DbType.String,255),
					new SQLiteParameter("@IsVerify", DbType.Boolean,1),
					new SQLiteParameter("@Rank", DbType.Int32,4),
					new SQLiteParameter("@ParentID", DbType.Int32,4),
					new SQLiteParameter("@TypeID", DbType.Int32,4),
					new SQLiteParameter("@IsUrl", DbType.Boolean,1),
					new SQLiteParameter("@Url", DbType.String,255),
                    new SQLiteParameter("@ID", DbType.Int32,4)};
            parameters[0].Value = model.Title;
            parameters[1].Value = model.KeyWords;
            parameters[2].Value = model.Description;
            parameters[3].Value = model.KeyName;
            parameters[4].Value = model.TempLate;
            parameters[5].Value = model.ChildTempLate;
            parameters[6].Value = model.IsVerify;
            parameters[7].Value = model.Rank;
            parameters[8].Value = model.ParentID;
            parameters[9].Value = model.TypeID;
            parameters[10].Value = model.IsUrl;
            parameters[11].Value = model.Url;
            parameters[12].Value = model.ID;

            object value = Db.Helper.ExecuteScalar(string.Concat("select Title from JNCms_Category where ID=", model.ID.ToString()));
            if (value != null && !value.ToString().Equals(model.Title, StringComparison.OrdinalIgnoreCase))
            {
                SQLiteParameter[] parameters1 = { new SQLiteParameter("@Title", DbType.String, 200), new SQLiteParameter("@ID", DbType.Int32, 4) };
                parameters1[0].Value = model.Title;
                parameters1[1].Value = model.ID;
                Db.Helper.ExecuteScalar(CommandType.Text, "update JNCms_Article set ChannelName=@Title where ChannelID=@ID", parameters1);
            }
            return Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Category set [Title]=@Title,[KeyWords]=@KeyWords,[Description]=@Description,[KeyName]=@KeyName,[TempLate]=@TempLate,[ChildTempLate]=@ChildTempLate,[IsVerify]=@IsVerify,[Rank]=@Rank,[ParentID]=@ParentID,[TypeID]=@TypeID,[IsUrl]=@IsUrl,[Url]=@Url where [ID]=@ID", parameters);
        }

        public int Delete(int id)
        {
            return Db.Helper.ExecuteNonQuery(CommandType.Text, "delete from JNCms_Category where [ID]=@ID", new SQLiteParameter("@ID", id));
        }

        public List<Models.Category> GetList(int parent, bool hasChild)
        {
            List<Models.Category> list = new List<Models.Category>();
            GetList(list, parent, hasChild);
            return list;
        }

        public List<Models.Category> GetList(int parent)
        {
            return GetList(parent, false);
        }

        private void GetList(List<Models.Category> list, int parent, bool hasChild)
        {
            using (IDataReader rd = Db.Helper.ExecuteReader(CommandType.Text, "select * from JNCms_Category where [ParentID]=@ID Order by Rank desc ", new SQLiteParameter("@ID", parent)))
            {
                while (rd.Read())
                {
                    list.Add(db.Entity.PopulateEntity<Models.Category>(rd));
                    if (hasChild)
                    {
                        GetList(list, list[list.Count - 1].ID, hasChild);
                    }
                }
            }
        }

        public Models.Category GetItem(string item)
        {
            using (IDataReader rd = Db.Helper.ExecuteReader(CommandType.Text, "select * from [JNCms_Category] where [KeyName]=@ID limit 1", new SQLiteParameter("@ID", item)))
            {
                if (rd.Read())
                {
                    return Db.Entity.PopulateEntity<Models.Category>(rd);
                }
            }
            return null;
        }

        public Models.Category GetItem(int id)
        {
            using (IDataReader rd = Db.Helper.ExecuteReader(CommandType.Text, "select * from [JNCms_Category] where [ID]=@ID limit 1", new SQLiteParameter("@ID", id)))
            {
                if (rd.Read())
                {
                    return Db.Entity.PopulateEntity<Models.Category>(rd);
                }
            }
            return null;
        }
    }
}
